<html>
	<head>
	    <!--Load the AJAX API-->
	    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
	    <script type="text/javascript">
	    	var alumni,grades,alumni_grade, career_subject;
	    	var mayorTable,grouped_subjects,alumniTable;
	    	var lineChart;
	    	var lineChartData;
	    	var grouped_subjects;
	    	var dataViz;
			var minYear = 2005;
			var maxYear = 2012;
			var min_avg;
			var max_avg;
			var annotatedtimeline;
			var cantRows = (maxYear - minYear)*12;
	    	// Load the Visualization API and the piechart package.
	    	google.load('visualization', '1.0', {packages:['table','controls','corechart']});
	      
	    	// Set a callback to run when the Google Visualization API is loaded.
	    	google.setOnLoadCallback(initialize);
		    
	    	function initialize() {
        		var query = new google.visualization.Query('https://docs.google.com/spreadsheet/pub?key=0Ar2w8seFCoUYdG9NS2N5UmdVbml0aUI4eHluRzVkSnc&output=csv');
        		query.send(handleQueryResponseForAlumni);
        	}
        
    		function handleQueryResponseForAlumni(response) {
    			if (response.isError()) {
        			alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
        			return;
        		}
	  
        		alumni = response.getDataTable();
				var query = new google.visualization.Query('https://docs.google.com/spreadsheet/pub?key=0Ar2w8seFCoUYdFl0d0lOYTdNVWIzWlAxY1lXRDNQcFE&output=csv');
	        	query.send(handleQueryResponseForGrades);
	        }
	        
	        function handleQueryResponseForGrades(response) {
		        if (response.isError()) {
			        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());
	        		return;
	        	}
		  
	        	grades = response.getDataTable();
	        	
				alumni_grade=google.visualization.data.join(grades, alumni,'inner',[[0,0]],[1,2,3,4],[1,2,3]);
				career_subject=google.visualization.data.join(grades, alumni,'inner',[[0,0]],[2],[2]);
				career_subject = google.visualization.data.group(career_subject, [1, 2]);
				min_avg = 0 ;
				max_avg = 10 ;
				buildChartControls()
	        	buildSubjectTable();
	        	buildAlumniTable();	    
	        	buildCareerTable();	         
				drawVisualization();
	        }
	        
	        function buildChartControls() {
		        document.getElementById("min_avg").onclick = function() {
			        var min_field=document.getElementById("min_avg");
			        var index = min_field.selectedIndex;
			        min_avg=parseInt(min_field[index].text);
			        if(annotatedtimeline!=undefined) {
				        printViz();
			        }
		        }
		        document.getElementById("max_avg").onclick = function() {
			        var max_field=document.getElementById("max_avg");
			        var index = max_field.selectedIndex;
			        max_avg = parseInt(max_field[index].text);
			        if(annotatedtimeline!=undefined) {
				        printViz();
			        }
		        }
	        }

	        function buildCareerTable() {
	        	grouped_Career = google.visualization.data.group(alumni, [2]);
	        	careerTable = new google.visualization.Table(document.getElementById('career_table'));
	        	grouped_Career.setColumnLabel(0, 'Carrera');
	        	grouped_Career.addColumn('boolean', 'Selected');
	        	for(index=0;index<grouped_Career.getNumberOfRows();++index) {
	        		grouped_Career.setCell(index,1,false);
	        	}	        	
	        	careerTable.draw(grouped_Career, null);
  				google.visualization.events.addListener(careerTable, 'select',selectionEventCareer);
	        }
	        

	        function selectionEventCareer() {
		        var item = careerTable.getSelection()[0];
				for (var i = 0; i < careerTable.getSelection().length; i++) {
					var item = careerTable.getSelection()[i];
					var previousValue = grouped_Career.getFormattedValue(item.row, 1);
					if(previousValue=='false'){
						grouped_Career.setValue(item.row, 1,true);
						addCareerToViz(item);
					} else {
						grouped_Career.setValue(item.row, 1,false);
						removeCareerFromViz(item);
						
					}

					careerTable.draw(grouped_Career,null);
				}
	        }      

			function removeCareerFromViz(item) {
				for(row = 1; row < dataViz.getNumberOfColumns(); row ++) {
					if(dataViz.getColumnLabel(row) == grouped_Career.getValue(item.row,0))
						dataViz.removeColumn(row);
				}
				printViz();	            
	        }
	        
	        function addCareerToViz(item) {
				var index = dataViz.addColumn('number', grouped_Career.getValue(item.row,0));
				for(cuat = 0; cuat < cantRows; cuat++){
					var average = getCareerAverage(grouped_Career.getValue(item.row,0), dataViz.getValue(cuat, 0));
					if(average >= 0 && average >=min_avg && average <= max_avg)
      						dataViz.setCell(cuat, index, average);   
				} 	
				printViz();	            
	        }
	        
	        function buildAlumniTable() {
	        	grouped_alumni = google.visualization.data.group(alumni, [1]);
	        	alumniTable = new google.visualization.Table(document.getElementById('alumni_table'));
	        	grouped_alumni.setColumnLabel(0, 'Alumno');
	        	grouped_alumni.addColumn('boolean', 'Selected');
	        	for(index=0;index<grouped_alumni.getNumberOfRows();++index) {
	        		grouped_alumni.setCell(index,1,false);
	        	}	        	
	        	alumniTable.draw(grouped_alumni, null);
  				google.visualization.events.addListener(alumniTable, 'select',selectionEventAlumni);
	        }
	        
	        function selectionEventAlumni() {
		        var item = alumniTable.getSelection()[0];
				for (var i = 0; i < alumniTable.getSelection().length; i++) {
					var item = alumniTable.getSelection()[i];
					var previousValue = grouped_alumni.getFormattedValue(item.row, 1);
					if(previousValue=='false'){
						grouped_alumni.setValue(item.row, 1,true);
						addAlumniToViz(item);
						
					} else {
						grouped_alumni.setValue(item.row, 1,false);
						removeAlumniFromViz(item);
					}
					alumniTable.draw(grouped_alumni,null);
				}
	        }
	        
	        function removeAlumniFromViz(item) {
				for(row = 1; row < dataViz.getNumberOfColumns(); row ++) {
					if(dataViz.getColumnLabel(row) == grouped_alumni.getValue(item.row,0))
						dataViz.removeColumn(row);
				}
				printViz();	            
	        }
	        
	        function addAlumniToViz(item) {
				var index = dataViz.addColumn('number', grouped_alumni.getValue(item.row,0));
				for(cuat = 0; cuat < cantRows; cuat++){
					var average = getAlumniAverage(grouped_alumni.getValue(item.row,0), dataViz.getValue(cuat, 0));
					if(average >= 0 && average >=min_avg && average <= max_avg)
      						dataViz.setCell(cuat, index, average);   
				} 	
				printViz();	            
	        }
	        
	        function buildSubjectTable() {
	        	grouped_subjects = google.visualization.data.group(grades, [2, 1]);
	        	subjectTable = new google.visualization.Table(document.getElementById('subject_table'));
	 	       	grouped_subjects.setColumnLabel(1, 'Codigo');
	        	grouped_subjects.setColumnLabel(0, 'Materia');
	        	grouped_subjects.insertColumn(1,'boolean', 'Selected');
	        	for(index=0;index<grouped_subjects.getNumberOfRows();++index) {
	        		grouped_subjects.setCell(index,1,false);
	        	}
	        	subjectTable.draw(grouped_subjects, null);  				
  				google.visualization.events.addListener(subjectTable, 'select',selectionEventSubjects);
	        }
	        
	        function selectionEventSubjects() {
		        var item = subjectTable.getSelection()[0];
				for (var i = 0; i < subjectTable.getSelection().length; i++) {
					var item = subjectTable.getSelection()[i];
					var previousValue = grouped_subjects.getFormattedValue(item.row, 1);
					if(previousValue=='false'){
						grouped_subjects.setValue(item.row, 1,true);
						addSubjectToVisualization(item);
					}
					else {
						grouped_subjects.setValue(item.row, 1,false);
						removeSubjectFromVisualization(item);						
					}
					subjectTable.draw(grouped_subjects,null);
				}
	        }
	        
	        
			function removeSubjectFromVisualization(item) {
				for(row = 1; row < dataViz.getNumberOfColumns(); row ++) {
					if(dataViz.getColumnLabel(row) == grouped_subjects.getValue(item.row,0))
						dataViz.removeColumn(row);
				}
				printViz();	            
	        }
	        
	        function addSubjectToVisualization(item) {
				var index = dataViz.addColumn('number', grouped_subjects.getValue(item.row,0)); 
				for(cuat = 0; cuat < cantRows; cuat++){
					var average = getSubjectAverage(grouped_subjects.getValue(item.row,0), dataViz.getValue(cuat, 0));
					if(average >= 0 && average >=min_avg && average <= max_avg)
      						dataViz.setCell(cuat, index, average);   
				} 	
				printViz();	            
	        }
	        
	        function printViz(){
	            var options = {
	                    title: 'Notas del ITBA',
	                    hAxis: {title: 'Cuatrimestre'},
	                    vAxis: {title: 'Nota', gridlines: {'count':15},minValue: min_avg, maxValue: max_avg},
	                    legend: 'true',
	                    interpolateNulls: 'true'
	                  };
	            
	            annotatedtimeline = new google.visualization.LineChart(
	                document.getElementById('chart_div'));
	            annotatedtimeline.draw(dataViz, options);
	        }
	        
	        
	        function drawVisualization() {
	        	dataViz = new google.visualization.DataTable();
	        	dataViz.addColumn('date', 'Cuatrimestre');
	        	dataViz.addColumn('number', 'Min de aprobacion');

	        	/*for(index=0;index<grouped_Career.getNumberOfRows();index++) {
        			if(grouped_Career.getValue(index, 1) == true)
						dataViz.addColumn('number', grouped_Career.getValue(index,0));        				
        		}
	        	for(index=0;index<grouped_subjects.getNumberOfRows();index++) {
        			if(grouped_subjects.getValue(index, 1) == true)
						dataViz.addColumn('number', grouped_subjects.getValue(index,0));        				
        		}
	        	for(index=0;index<grouped_alumni.getNumberOfRows();index++) {
        			if(grouped_alumni.getValue(index, 1) == true)
						dataViz.addColumn('number', grouped_alumni.getValue(index,0));        				
        		}*/
				var i = 0;
				dataViz.addRows(cantRows);
				for(year = minYear; year < maxYear; year ++)
					for(month = 0; month < 12; month ++){
						dataViz.setCell(i, 0, new Date(year, month, 1));
						dataViz.setCell(i++, 1, 4);
					}
				
				
			/*	var career = 2;
				for(index=0;index<grouped_Career.getNumberOfRows();++index) {
					if(grouped_Career.getValue(index, 1) == true){
						for(cuat = 0; cuat < cantRows; ++cuat){
							var average = getCareerAverage(grouped_Career.getValue(index,0), dataViz.getValue(cuat, 0));
							if(average >= 0)
        						dataViz.setCell(cuat, career, average);   
						}
						career++;
					}
        		}
				var subject = career;
				for(index=0;index<grouped_subjects.getNumberOfRows();++index) {
					if(grouped_subjects.getValue(index, 1) == true){
						for(cuat = 0; cuat < cantRows; ++cuat){
							var average = getSubjectAverage(grouped_subjects.getValue(index,0), dataViz.getValue(cuat, 0));
							if(average >= 0)
        						dataViz.setCell(cuat, subject, average);   
						}
						subject++;
					}
        		}
				var alumni = subject;
				for(index=0;index<grouped_alumni.getNumberOfRows();++index) {
					if(grouped_alumni.getValue(index, 1) == true){
						for(cuat = 0; cuat < cantRows; ++cuat){
							var average = getAlumniAverage(grouped_alumni.getValue(index,0), dataViz.getValue(cuat, 0));
							if(average >= 0)
        						dataViz.setCell(cuat, alumni, average);   
						}
						alumni++;
					}
        		}*/

				printViz();		            
	          }
	        
	        function getCareerAverage(career, date){
	        	var total = 0;
	        	var cant = 0;
				for (var i = 0; i < career_subject.getNumberOfRows(); i++) {
 					if((career_subject.getValue(i, 1) == career)/* && sameMonth(date, alumni.getValue(i, 3))*/){
					/*	console.log(career_subject.getValue(i, 0));
						total = total + getSubjectAverage(career_subject.getValue(i, 0), date);
						cant++;
					}*/
					for (var j = 0; j < grades.getNumberOfRows(); j++) {					
						if((grades.getValue(j, 2) == career_subject.getValue(i, 0)) && sameMonth(date, grades.getValue(j, 3))){
							total = total + grades.getValue(j, 4);
							cant++;
						}
						
					}
				}
						
				}
				if(cant > 0)
					return total/cant;
				return -1;
	        }
	        
	        function getSubjectAverage(subject, date){
	        	var total = 0;
	        	var cant = 0;
				for (var i = 0; i < grades.getNumberOfRows(); i++) {					
					if((grades.getValue(i, 2) == subject) && sameMonth(date, grades.getValue(i, 3))){
						total = total + grades.getValue(i, 4);
						cant++;
					}
						
				}
				if(cant > 0)
					return total/cant;
				return -1;
	        }
	        
	        function getAlumniGeneralAverage(alumni){
	        	var total = 0;
	        	var cant = 0;
				for (var i = 0; i < alumni_grade.getNumberOfRows(); i++) {
					if(alumni_grade.getValue(i, 5) == alumni){
						total = total + alumni_grade.getValue(i, 4);
						cant++;
					}						
				}
				if(cant > 0)
					return total/cant;
				return -1;
	        }
	        
	        function getAlumniAverage(alumni, date){
	        	var total = 0;
	        	var cant = 0;
				for (var i = 0; i < alumni_grade.getNumberOfRows(); i++) {
					if((alumni_grade.getValue(i, 5) == alumni) && sameMonth(date, alumni_grade.getValue(i, 3))){
						total = total + alumni_grade.getValue(i, 4);
						cant++;
					}
						
				}
				if(cant > 0)
					return total/cant;
				return -1;
	        }
	        
	        function sameMonth(dateA , dateB){
	        	if(dateA.getFullYear() == dateB.getFullYear() && dateA.getMonth() == dateB.getMonth()){
        			return true;
	        	}	        	
	        	return false;   	
	        }


	    </script>
	</head>

	<body>
		<!--Div that will hold the pie chart-->
		<div id="chart_div" style="width:800; height:400"></div>
		<select id="min_avg">
			<option value="0" selected="true">0</option>
			<option value="1">1</option>
			<option value="2">2</option>
			<option value="3">3</option>
			<option value="4">4</option>
			<option value="5">5</option>
			<option value="6">6</option>
			<option value="7">7</option>
			<option value="8">8</option>
			<option value="9">9</option>
			<option value="10">10</option>
		</select>
		<select id="max_avg">
			<option value="0" selected="true">0</option>
			<option value="1">1</option>
			<option value="2">2</option>
			<option value="3">3</option>
			<option value="4">4</option>
			<option value="5">5</option>
			<option value="6">6</option>
			<option value="7">7</option>
			<option value="8">8</option>
			<option value="9">9</option>
			<option value="10" selected="true">10</option>
		</select>
		<div style="float:left;"><div id="subject_table" style="width:400; height:300"></div></div>
		<div style="float:left;"><div id="alumni_table" style="width:400; height:300"></div></div>
		<div style="float:left;"><div id="career_table" style="width:400; height:300"></div></div>
	</body>
	
</html>